package approve.dao;
import java.sql.ResultSet;
import java.util.ArrayList;
import models.util.MySqlDataAccessHelper;
import approve.model.Advance;
public class AdvanceDAO {
	public static ArrayList<Advance> getListAdvance(String id,String database){
		ArrayList<Advance> lst = new ArrayList<Advance>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			helper.open(database);
			String sql = "select * from ap_advance where document_id = '"+id+"'";
			ResultSet rs = helper.executeQuery(sql);
			while(rs.next()){
				Advance a = new Advance();
				a.setId(rs.getString("id"));
				a.setItems(rs.getString("items"));
				a.setContent(rs.getString("content"));
				a.setNotes(rs.getString("notes"));
				a.setMoney(rs.getDouble("money"));
				a.setDocument_id(id);
				lst.add(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			helper.close();
		}
		return lst;
	}
	
	public static float getTotalAmount(String databasename, String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		float totalAmount = 0.0f;
		try {
			String sql = "select SUM(ad.money) as money  from ap_advance ad where ad.document_id='"+iDApproveDoc+"' limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				totalAmount = rs.getFloat("money");
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return totalAmount;
	}
}